package com.boilerCloud.util;

import java.sql.Connection;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.apache.commons.lang.NullArgumentException;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.datasource.DataSourceUtils;

public class SQLUtil {
	
	// 定义数据库类型
	public static String DBMS_ORACLE_TYPE = "oracle";
	public static String DBMS_SQLSERVER_TYPE = "sqlserver";
	public static String DBMS_DB2_TYPE = "db2";
	public static String DBMS_MYSQL_TYPE = "mysql";
	public final static String POSTGRESQL="postgresql";
	private static String dbType = "";
	
	public SQLUtil() throws Exception {
	}

	/*
	 * 限制：在标准SQL里不能出现的字段或表别名：表别名pageView; 字段pageNum @function 得到不同数据库的分页技术
	 * 
	 * @param sql :标准的SQL语句 @param pageStart 页开始 @param pageSize 页大小 @param
	 * db_type 数据库类型 @return sql :带分页的SQL语句
	 */
	public static String getPageSQL(String sql, long pageStart, int pageSize, String db_type) {
		long pageEnd = pageStart + pageSize;
		StringBuffer retStr = new StringBuffer(sql);
		if (db_type.equals(DBMS_ORACLE_TYPE)) {
			boolean hasOffset = true;
			sql = sql.trim();
			StringBuffer pagingSelect = new StringBuffer(sql.length() + 100);
			if (hasOffset) {
				pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");
			} else {
				pagingSelect.append("select * from ( ");
			}
			pagingSelect.append(sql);
			if (hasOffset) {
				pagingSelect.append(" ) row_ where rownum <= " + pageEnd + ") where rownum_ > " + pageStart + "");
			} else {
				pagingSelect.append(" ) where rownum <= " + pageSize + "");
			}

			return pagingSelect.toString();

		} else if (db_type.equals(DBMS_SQLSERVER_TYPE)) {
			StringBuffer pagingSelect = new StringBuffer(sql.length() + 100);
			String orderbyParam = " id desc";
			String [] aa = sql.split("order by");
			if(aa.length>1){
				orderbyParam = aa[1];
			}
			sql = aa[0];
			pagingSelect.append("select * from ( select row_number () over (order by ");
			pagingSelect.append(orderbyParam );
			pagingSelect.append(" ) n,* from ( ");
			pagingSelect.append(sql);
			pagingSelect.append(") tmp) tmp2 where tmp2.n between ");
			pagingSelect.append(pageStart+1);
			pagingSelect.append(" and ");
			pagingSelect.append(pageStart+pageSize);
			return pagingSelect.toString();
		} else if (db_type.equals(DBMS_DB2_TYPE)) {

		} else if (db_type.equals(DBMS_MYSQL_TYPE)) {
			return sql + " limit " + pageSize + " offset " + pageStart + "";
		}
		return retStr.toString();
	}
	/*public static void main(String[] args) {
		System.out.println(System.getProperty("user.dir"));
	}*/

	/**
	 * @方法功能说明：获取标准的sql分页语句
	* @author	zzy 
	* @date	2015-3-20下午4:16:44 
	* @modifier	
	* @modify_description	
	* @param sql 待分页的语句
	* @param pageNum 当前页码
	* @param pageLength 每页显示记录数
	* @return 适配各个数据的标准sql语句
	* @version v1.0.0 
	 * @throws Exception 
	* @since v1.0.6
	 */
	public static String createPageSql(StringBuilder sql,int pageNum,int pageLength,DataSource ds) throws Exception{
		if(sql==null||StringUtils.isBlank(sql.toString())) throw new NullArgumentException("sql语句为空无法获取标准的分页语句");
		
		dbType = getDBType(ds);
		if(StringUtils.isBlank(dbType))throw new Exception("暂不支持数据库类型为："+dbType+"的sql分页实现");
		if (dbType.equalsIgnoreCase(DBMS_ORACLE_TYPE)) {
			StringBuilder temp = new StringBuilder();
			temp.append("select tt.*,ROWNUM from (");
				temp.append("select t.*,ROWNUM as rn  from ( ");
					temp.append(sql);
				temp.append(" ) t ");
				temp.append(" where ROWNUM<="+(pageNum*pageLength));
			temp.append(") where rn>"+(pageNum-1)*pageLength);
			return temp.toString();
		} else if (dbType.equalsIgnoreCase(DBMS_SQLSERVER_TYPE)) {
			
			
		} else if (dbType.equalsIgnoreCase(DBMS_MYSQL_TYPE) || dbType.equalsIgnoreCase(POSTGRESQL)) {
			sql.append(" limit "+pageLength+" offset "+(pageNum-1)*pageLength);
		} else {
			throw new Exception("暂不支持数据库类型为："+dbType+"的sql分页实现");
		}
		return sql.toString();
	}
	
	private static String getDBType(DataSource ds){
		Connection con = null;
		try {
			con = DataSourceUtils.getConnection(ds);
			if(con!=null) return con.getMetaData().getDatabaseProductName();
			return "";
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return "";
		}finally{
			DataSourceUtils.releaseConnection(con, ds);
		}
	}
	
	private static String getDBType(String rawUrl){
		if (rawUrl == null) {
            return null;
        }
		if (rawUrl.startsWith("jdbc:mysql:")) {
            return DBMS_MYSQL_TYPE;
        } else if (rawUrl.startsWith("jdbc:oracle:")) {
            return DBMS_ORACLE_TYPE;
        } else if (rawUrl.startsWith("jdbc:alibaba:oracle:")) {
            return "AliOracle";
        } else if (rawUrl.startsWith("jdbc:microsoft:")) {
            return DBMS_SQLSERVER_TYPE;
        } else if (rawUrl.startsWith("jdbc:postgresql:")) {
            return POSTGRESQL;
        } else if (rawUrl.startsWith("jdbc:hsqldb:")) {
            return "hsql";
        } else if (rawUrl.startsWith("jdbc:db2:")) {
            return "db2";
        } else if (rawUrl.startsWith("jdbc:sqlite:")) {
            return "sqlite";
        } else if (rawUrl.startsWith("jdbc:ingres:")) {
            return "ingres";
        } else if (rawUrl.startsWith("jdbc:h2:")) {
            return "h2";
        } else if (rawUrl.startsWith("jdbc:mckoi:")) {
            return "mckoi";
        } else if (rawUrl.startsWith("jdbc:cloudscape:")) {
            return "cloudscape";
        } else if (rawUrl.startsWith("jdbc:informix-sqli:")) {
            return "informix";
        } else if (rawUrl.startsWith("jdbc:timesten:")) {
            return "timesten";
        } else if (rawUrl.startsWith("jdbc:as400:")) {
            return "as400";
        } else if (rawUrl.startsWith("jdbc:sapdb:")) {
            return "sapdb";
        } else if (rawUrl.startsWith("jdbc:JSQLConnect:")) {
            return "JSQLConnect";
        } else if (rawUrl.startsWith("jdbc:JTurbo:")) {
            return "JTurbo";
        } else if (rawUrl.startsWith("jdbc:firebirdsql:")) {
            return "firebirdsql";
        } else if (rawUrl.startsWith("jdbc:interbase:")) {
            return "interbase";
        } else if (rawUrl.startsWith("jdbc:pointbase:")) {
            return "pointbase";
        } else if (rawUrl.startsWith("jdbc:edbc:")) {
            return "edbc";
        } else if (rawUrl.startsWith("jdbc:mimer:multi1:")) {
            return "mimer";
        } else {
            return null;
        }
		
	}
	
	

	/**
	 * sql server分页方法
	 * @param sql
	 * @param pageStart
	 * @param pageSize
	 * @param orderbyParam	排序字段
	 * @return
	 */
	public static String getPage_SqlServer(String sql,long pageStart, int pageSize,String orderbyParam  ){
		StringBuffer pagingSelect = new StringBuffer(sql.length() + 100);
		pagingSelect.append("select * from ( select row_number () over (order by ");
		pagingSelect.append(orderbyParam );
		pagingSelect.append(" ) n,* from ( ");
		pagingSelect.append(sql);
		pagingSelect.append(") tmp) tmp2 where tmp2.n between ");
		pagingSelect.append(pageStart+1);
		pagingSelect.append(" and ");
		pagingSelect.append(pageStart+pageSize);
		
		return pagingSelect.toString();
	}
	/**
	 * sql server分页方法
	 * @param sql
	 * @param pageStart
	 * @param pageSize
	 * @param orderbyParam	排序字段
	 * @return
	 */
	public static String getPage_SqlServer2(String sql,long pageStart, int pageSize,String orderbyParam  ){
		StringBuffer pagingSelect = new StringBuffer(sql.length() + 100);
		pagingSelect.append("select * from ( select row_number () over (order by ");
		pagingSelect.append(orderbyParam );
		pagingSelect.append(" desc ) n,* from ( ");
		pagingSelect.append(sql);
		pagingSelect.append(") tmp) tmp2 where tmp2.n between ");
		pagingSelect.append(pageStart+1);
		pagingSelect.append(" and ");
		pagingSelect.append(pageStart+pageSize);
		
		return pagingSelect.toString();
	}
}
